package com.ideal.gpt.app.local;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.lzwork.utility.file.FileUtility;
import com.lzwork.utility.file.LineContent;
import com.lzwork.utility.officeparser.ExcelReader;
import com.lzwork.utility.officeparser.ExcelReader.Line;
import com.lzwork.utility.officeparser.ExcelReader.XlsData;
import com.lzwork.utility.officeparser.ExcelSaver;
import com.lzwork.utility.str.StrUtility;

/**
 * @author LiZheng
 * @date 2023年11月3日 下午5:47:21
 */

public class DbJsonToExcel extends ExcelSaver
{
	public static void doConvert(File input, File output)
	{
		String fileStr = LineContent.loadString(input);
		JSONObject obj = JSONObject.parseObject(fileStr);

		Map<String, Integer> indexMap = new HashMap<>();

		Workbook wb = createXlsFile(output);
		Sheet sheet = wb.createSheet();

		JSONArray rows = obj.getJSONArray("rows");
		Row head = pickNewRow(sheet);

		int rowI = 0;

		System.out.println("Total rows get: " + rows.size());
		int index = 0;

		for (Object or : rows)
		{
			JSONObject o = (JSONObject) or;
			Row row = pickNewRow(sheet);
			index++;

			for (Entry<String, Object> e : o.entrySet())
			{
				if (e.getValue() == null)
				{
					continue;
				}

				String key = e.getKey();
				String v = e.getValue().toString();

				v = ClearFile.clearCell(v);
				v = ClearFile.normalize(v);

				int i = 0;

				if (indexMap.containsKey(key))
					i = indexMap.get(key);
				else
				{
					i = indexMap.size();
					indexMap.put(key, i);

					Cell f = pickCellAt(head, i);
					f.setCellValue(key);
				}

				try
				{
					Cell c = pickCellAt(row, i);
					c.setCellValue(v);
				} catch (Exception e2)
				{
					System.out.println("Error at row " + rowI + " at cell " + key);
				}
			}

			if (index % 100 == 0)
				System.out.println(index + "/" + rows.size() + " rows checked.");

			rowI++;
		}

		System.out.println("Parse done " + rowI);

		saveXlsFile(wb, output);
	}

	public static void saveToFiles(File input, File folder)
	{
		XlsData pages = ExcelReader.read(input);

		int saved = 0;
		int skip = 0;

		List<Line> lines = pages.allLines();
		int total = lines.size();

		System.out.println("Saving files from " + total + " lines.");

		for (Line line : lines)
		{
			String name = line.getFieldData("KL_ID").trim();
			name = name.replace("/", "");
			String content = line.getFieldData("KL_CONTENT");

			if (!StrUtility.stringValid(content))
			{
				skip++;
				continue;
			}

			File outFile = FileUtility.getFileSafe(folder, name + ".txt");
			LineContent.saveFile(outFile, content, false, false, "\n");
			saved++;

			if (saved % 100 == 0)
			{
				System.out.println(saved + "/" + total + " files saved.");
			}
		}

		System.out.println("Total " + saved + " files saved. " + skip + " files skipped.");
	}

	public static void main(String[] args)
	{
		File input = new File("v:/12345/qadb_content.json");
		File output = new File("v:/12345/qqqq500.xlsx");

		doConvert(input, output);

		// File input = new File("v:/12345/qadb.xlsx");
		File outFolder = new File("v:/12345/tocheck");
		outFolder = new File("v:/12345/source500");

		saveToFiles(output, outFolder);

	}
}
